--    Program: 		CAPARIMP_RECEIPT_BATCH.sql
--    Description:  Import AR RECEIPT BATCH information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_batch_id				number;
                    		                 		
   cursor cur_ar_receipt_batch is
      select * from CRST_AR_BATCHES_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_receipt_batch loop   	
     l_err_msg_tmp := '';
     
     SELECT AR_BATCHES_S.NEXTVAL 
     INTO l_batch_id
     FROM DUAL;
     
     update CRST_AR_BATCHES_ALL
     set    batch_id_n = l_batch_id
     where  batch_id = cur.BATCH_ID;
     
     begin
       -- import data
       insert into AR_BATCHES_ALL
       (
          BATCH_ID                    ,
          LAST_UPDATED_BY             ,
          LAST_UPDATE_DATE            ,
          LAST_UPDATE_LOGIN           ,
          CREATED_BY                  ,
          CREATION_DATE               ,
          NAME                        ,
          BATCH_DATE                  ,
          GL_DATE                     ,
          STATUS                      ,
          DEPOSIT_DATE                ,
          CLOSED_DATE                 ,
          TYPE                        ,
          BATCH_SOURCE_ID             ,
          CONTROL_COUNT               ,
          CONTROL_AMOUNT              ,
          BATCH_APPLIED_STATUS        ,
          CURRENCY_CODE               ,
          EXCHANGE_RATE_TYPE          ,
          EXCHANGE_DATE               ,
          EXCHANGE_RATE               ,
          TRANSMISSION_REQUEST_ID     ,
          LOCKBOX_ID                  ,
          LOCKBOX_BATCH_NAME          ,
          COMMENTS                    ,
          ATTRIBUTE_CATEGORY          ,
          ATTRIBUTE1                  ,
          ATTRIBUTE2                  ,
          ATTRIBUTE3                  ,
          ATTRIBUTE4                  ,
          ATTRIBUTE5                  ,
          ATTRIBUTE6                  ,
          ATTRIBUTE7                  ,
          ATTRIBUTE8                  ,
          ATTRIBUTE9                  ,
          ATTRIBUTE10                 ,
          MEDIA_REFERENCE             ,
          OPERATION_REQUEST_ID        ,
          RECEIPT_METHOD_ID           ,
          REMITTANCE_BANK_ACCOUNT_ID  ,
          RECEIPT_CLASS_ID            ,
          ATTRIBUTE11                 ,
          ATTRIBUTE12                 ,
          ATTRIBUTE13                 ,
          ATTRIBUTE14                 ,
          ATTRIBUTE15                 ,
          PROGRAM_APPLICATION_ID      ,
          PROGRAM_ID                  ,
          PROGRAM_UPDATE_DATE         ,
          REMITTANCE_BANK_BRANCH_ID   ,
          REMIT_METHOD_CODE           ,
          REQUEST_ID                  ,
          SET_OF_BOOKS_ID             ,
          TRANSMISSION_ID             ,
          BANK_DEPOSIT_NUMBER         ,
          ORG_ID                      ,
          PURGED_CHILDREN_FLAG        ,
          WITH_RECOURSE_FLAG          ,
          AUTO_PRINT_PROGRAM_ID       ,
          AUTO_TRANS_PROGRAM_ID        
       )
       values
       (
            l_batch_id                      ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_DATE            ,
            cur.LAST_UPDATE_LOGIN           ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE               ,
            cur.NAME                        ,
            cur.BATCH_DATE                  ,
            cur.GL_DATE                     ,
            cur.STATUS                      ,
            cur.DEPOSIT_DATE                ,
            cur.CLOSED_DATE                 ,
            cur.TYPE                        ,
            CAPARIMP_MAPPING.GetReceiptSourceIDMapping(cur.ORG_ID, cur.BATCH_SOURCE_ID),
            cur.CONTROL_COUNT               ,
            cur.CONTROL_AMOUNT              ,
            cur.BATCH_APPLIED_STATUS        ,
            cur.CURRENCY_CODE               ,
            cur.EXCHANGE_RATE_TYPE          ,
            cur.EXCHANGE_DATE               ,
            cur.EXCHANGE_RATE               ,
            cur.TRANSMISSION_REQUEST_ID     ,
            cur.LOCKBOX_ID                  ,
            cur.LOCKBOX_BATCH_NAME          ,
            cur.COMMENTS                    ,
            cur.ATTRIBUTE_CATEGORY          ,
            cur.ATTRIBUTE1                  ,
            cur.ATTRIBUTE2                  ,
            cur.ATTRIBUTE3                  ,
            cur.ATTRIBUTE4                  ,
            cur.ATTRIBUTE5                  ,
            cur.ATTRIBUTE6                  ,
            cur.ATTRIBUTE7                  ,
            cur.ATTRIBUTE8                  ,
            cur.ATTRIBUTE9                  ,
            cur.ATTRIBUTE10                 ,
            cur.MEDIA_REFERENCE             ,
            cur.OPERATION_REQUEST_ID        ,
            cur.RECEIPT_METHOD_ID           ,
            CAPBRIMP_MAPPING.GetBankACMapping(cur.REMITTANCE_BANK_ACCOUNT_ID),
            cur.RECEIPT_CLASS_ID            ,
            cur.ATTRIBUTE11                 ,
            cur.ATTRIBUTE12                 ,
            cur.ATTRIBUTE13                 ,
            cur.ATTRIBUTE14                 ,
            cur.ATTRIBUTE15                 ,
            cur.PROGRAM_APPLICATION_ID      ,
            cur.PROGRAM_ID                  ,
            cur.PROGRAM_UPDATE_DATE         ,
            cur.REMITTANCE_BANK_BRANCH_ID   ,
            cur.REMIT_METHOD_CODE           ,
            cur.REQUEST_ID                  ,
            CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
            cur.TRANSMISSION_ID             ,
            cur.BANK_DEPOSIT_NUMBER         ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.PURGED_CHILDREN_FLAG        ,
            cur.WITH_RECOURSE_FLAG          ,
            cur.AUTO_PRINT_PROGRAM_ID       ,
            cur.AUTO_TRANS_PROGRAM_ID                          
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_RECEIPT_BATCH', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
